﻿using System;
using System.Data;
using System.Data.OracleClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace PDNPAS.db
{
    class DBHelper
    {
        //获取数据库的连接
        public static OracleConnection GetOracleConnection(string dbName, string userName, string userPwd)
        {
            OracleConnection orclConn;
            OracleConnectionStringBuilder OrclConStrB = new OracleConnectionStringBuilder();
            OrclConStrB.DataSource = dbName;
            OrclConStrB.UserID = userName;
            OrclConStrB.Password = userPwd;
            orclConn = new OracleConnection(OrclConStrB.ConnectionString);
            return orclConn;
        }

        //将查询到的结果集存到DataTable中，然后返回
        public static DataTable GetQueryResult(OracleConnection conn, string sql)
        {
            DataTable dtable = new DataTable();
            try
            {
                conn.Open();
                OracleDataAdapter orclDadapter = new OracleDataAdapter(sql, conn);
                orclDadapter.Fill(dtable);
            }
            catch (Exception e)
            {
                Console.WriteLine("error:" + e);
            }
            finally
            {
                conn.Close();
            }
            return dtable;
        }

        //向数据库插入一条记录
        public static void InsertRecord(OracleConnection conn, string sql)
        {
            try
            {
                conn.Open();
                OracleCommand com = new OracleCommand(sql, conn);
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("error:" + e);
            }
            finally
            {
                conn.Close();
            }
        }

        //修改数据库中的记录
        public static void UpdateRecord(OracleConnection conn, string sql)
        {
            try
            {
                conn.Open();
                OracleCommand com = new OracleCommand(sql, conn);
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("error:" + e);
            }
            finally
            {
                conn.Close();
            }
        }

        //删除数据库中的记录
        public static void DeleteRecord(OracleConnection conn, string sql)
        {
            try
            {
                conn.Open();
                OracleCommand com = new OracleCommand(sql, conn);
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("error:" + e);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
